import pymysql.sursors
import sqlcipher3 as sqlite3
import os
from typing import Optional, List, Dict, Any, Union

class DbError(Exception):
    '''数据库操作异常类。'''
    def __init__(self, *args):
        super().__init__(*args)

class MysqlTools:
    '''
    MYsql数据库工具集合，基本操作
    单例模式

    目前是每次都重新做连接，也可以优化为只进行一次连接
    那样的话需要做锁，还需要做断线重连

    情况还不明朗
    '''

    # 单例模式，只允许一个实例
    _instance = None

    def set(self, **kwargs):
        '''
        设置数据库凭据，注意实参需要包含
        hostname，database，username，password。
        '''
        self.hostname: str = kwargs['hostname']
        self.database: str = kwargs['database']
        self.username: str = kwargs['username']
        self.password: str = kwargs['password']

    def set_db(self, database: str):
        '''设置数据库的名字。用于临时改数据库

        优化建议： 删除'''
        self.database = database
        return self

    def __new__(cls, *args, **kwargs):
        '''魔法方法，创建新对象时调用。
        如果之前已经创建过了，就不再创建新的。'''
        if not cls._instance:
            cls._instance = super(MysqlTools, cls).__new__(cls)
        return cls._instance

    def __init__(self):
        # 这里可以添加初始化代码
        # 注意：每次使用MysqlTools() 时都会调用__init__()，即使返回的是同一个实例
        ...

    def exec_and_fetch_all(self, sql: str, data: tuple) -> tuple:
        '''执行sql语句并且拉取每一行结果，采用绑定式传参'''
        try:
            connection = pymysql.connect(
                    host=self.hostname,
                    user=self.username,
                    password=self.password,
                    database=self.database
            )

            with connection.cursor() as cursor:
                cursor.execute(sql, data)
                connection.commit()
                ret = cursor.fetchall()
                return ret
        except pymysql.MySQLError as e:
            raise DbError(str(e))
        finally:
            if "connection" in locals() and connection.open:
                connection.close()

    def chk(self) -> bool:
        '''通过拉取数据表的列表，检查数据库连接是否正确。'''
        try:
            tables = self.exec_and_fetch_all("SHOW TABLES;", ())
            return True
        except Exception as e:
            # print(f"mysql.chk: {e}")
            return False

class SqliteTools:
    '''
    Sqlite3 数据库的工具集合，基本操作

    单例模式， 支持加密数据库。

    示例用法:
        db = SqliteTools.get_instance("test.db", "password123")
        db.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
        db.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))
        results = db.query("SELECT * FROM users")
    '''

    # 单例模式的唯一实例
    _instance = None
    _initialized = False

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super(SqliteTools, cls).__new__(cls)
        return cls._instance

    def __init__(self):
        # 每次使用SqliteTools()时都会调用__init__，即使返回的是同一个实例
        if not self._initiallized:
            self.db_path = db_path
            self.password=password
            self.conn: Optional[sqlite3.Connection] = None
            self.cursor: Optional[sqlite3.Cursor] = None
            self._connect()
            self._initialized = True

    @classmethod
    def get_instance(cls, db_path: str = ":memory:", password: Optional[str] = None) -> SqliteTools:
        '''获取单例模式的更好方式'''
        if cls._instance is None:
            cls._instance = cls(db_path, password)
        return cls._instance

    def _connect(self) -> None:
        '''连接到数据库并设置密码。'''
        try:
            self.conn = sqlite3.connect(self.db_path)

            if self.password:
                self.conn.execute(f'PRAGMA key="{self.password}"')
                try: # 验证密码是否正确
                    self.conn.execute("SELECT count(*) FROM sqlite_master")
                except sqlite3.DatabaseError:
                    raise DbError('Wrong password or corrupted database')

            self.cursor = self.conn.cursor()
            # 开启外键支持
            self.execute("PRAGMA foreign_keys = ON")

        except Exception as e:
            raise DbError(f"Failed to connect to database: {str(e)}")

    def execute(self, sql: str, params: Union[tuple, list, dict] = ()) -> Optional[int]:
        '''
        执行SQL语句（INSERT， UPDATE， DELETE等）
        返回受影响的行数
        '''
        try:

            if isinstance(params, dict):
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql, params)

            self.conn.commit()
            return self.cursor.rowcount
        
        except Exception as e:
            self.conn.rollback()
            raise DbError(f"Execute SQL failed: {str(e)}")

    def def executemany(self, sql: str, params_list: List[Union[tuple, list, dict]]) -> Optional[int]:
        '''
        批量执行SQL语句
        返回受影响的总行数
        '''
        try:
            self.cursor.executemany(sql, params_list)
            self.conn.commit()
            return self.cursor.rowcount
        except Exception as e:
            self.conn.rollback()
            raise DbError(f"Execute manySQL failed: {str(e)}")

    def query(self, sql: str, params: Union[tuple, list, dict] = ()) -> List[Dict[str, Any]]:
        '''
        执行查询语句，返回字典列表
        每行数据作为一个字典，键为列名
        '''
        try:
            if isinstance(param, dict):
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql, params)

            # 获取列名
            column_names = [description[0] for description in self.cursor.description]

            # 将结果转换为字典列表
            results = []
            for row in self.cursor.fetchall():
                results.append(dict(zip(column_names, row)))

            return results

        except Exception as e:
            raise DbError(f"Query failed: {str(e)}")

    def query_one(self, sql: str, params: Union[tuple, list, dict] = ()) -> Optional[Dict[str, Any]]:
        '''
        执行查询语句，返回单行结果（字典）
        如果没有结果返回None
        '''
        results = self.query(sql, params)
        return results[0] if results else None

    def table_exists(self, table_name: str) -> bool:
        '''检查表是否存在。'''
        sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?"
        result = self.query_one(sql, (table_name,))
        return result is not None

    def close(self) -> None:
        '''关闭数据库连接。'''
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
        self.conn =None
        self.cursor=None
        self._initialized = False
        SqliteTools._instance = None

    def __enter__(self):
        return self

    def __exit(self, exc_type, exc_val, exc_tb):
        self.close()

    def __del__(self):
        self.close()

